/**
 * Copyright 2011 Clemens Lombriser <clemens@lom.ch>
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 * 
 */

package ch.lom.clemens.android.bibliography.data;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.*;
import android.util.Log;

public class DBConnector {
	
	private static final String [] CREATE_TABLES = { "CREATE TABLE COLLECTION (ID TEXT PRIMARY KEY, NAME TEXT NOT NULL, TYPE TEXT NOT NULL, SIZE INTEGER)",
	                                                 "CREATE TABLE COLLECTIONDOCS (COLLECTIONID TEXT, DOCUMENTID TEXT)",
	                                                 "CREATE TABLE GROUPS (ID TEXT PRIMARY KEY, NAME TEXT NOT NULL, TYPE TEXT NOT NULL, SIZE INTEGER)",
	                                                 "CREATE TABLE GROUPDOCS (GROUPID TEXT, DOCUMENTID TEXT)",
	                                                 "CREATE TABLE DOCUMENT (ID TEXT PRIMARY KEY, TITLE TEXT NOT NULL, PUBLICATION_OUTLET TEXT, YEAR INTEGER, ABSTRACT TEXT, DOI TEXT, URL TEXT, TYPE TEXT, NOTES TEXT)",
	                                                 "CREATE TABLE AUTHOR (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT)",
	                                                 "CREATE TABLE DOCAUTHOR (DOCID TEXT, AUTID INTEGER, POSITION INTEGER)",
	                                                 "CREATE TABLE TAG (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT)",
	                                                 "CREATE TABLE DOCTAG (DOCID TEXT, TAGID INTEGER)",
	                                                 "CREATE TABLE IDENTIFIER (DOCID TEXT, TYPE TEXT, VALUE TEXT)",
	                                                 "CREATE TABLE DISCIPLINE  (DOCID TEXT, TYPE TEXT, VALUE TEXT)"};
	
	private static final String DATABASE_NAME   = "MendeleyBibliography";
	private static final int    DATABASE_VERSION= 1;
	private static final String [] DROP_TABLES   = { "DROP TABLE IF EXISTS COLLECTION",
		                                             "DROP TABLE IF EXISTS COLLECTIONDOCS",
		                                             "DROP TABLE IF EXISTS GROUPS",
		                                             "DROP TABLE IF EXISTS GROUPDOCS",
		                                             "DROP TABLE IF EXISTS DOCUMENT",
		                                             "DROP TABLE IF EXISTS AUTHOR",
		                                             "DROP TABLE IF EXISTS DOCAUTHOR",
		                                             "DROP TABLE IF EXISTS TAG",
		                                             "DROP TABLE IF EXISTS DOCTAG",
		                                             "DROP TABLE IF EXISTS IDENTIFIER",
		                                             "DROP TABLE IF EXISTS DISCIPLINE"};
	
	private Context m_context;
	private DatabaseHelper m_dbaccess;
	private SQLiteDatabase m_database;
	
	public DBConnector(Context ctx) {
		m_context = ctx;
		m_dbaccess = new DatabaseHelper(m_context);
	}
	
	public DBConnector open() throws SQLException {
		m_database = m_dbaccess.getWritableDatabase();
		return this;
	}
	
	public void close() {
		m_dbaccess.close();
	}
	
	public void clear() {
		m_dbaccess.onClear(m_database);
	}

	
	//////////////////////////////////////////////////////////////////////////////
	// Database handling
	
    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        public void onCreate(SQLiteDatabase db) {
        	for (int i=0; i<CREATE_TABLES.length;i++){
        		db.execSQL(CREATE_TABLES[i]);        		
        	}
        }

        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w("DBConnector", "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
            onClear(db);
        }
        
        public void onClear(SQLiteDatabase db) {
            for (int i=0; i<DROP_TABLES.length;i++) {
            	db.execSQL(DROP_TABLES[i]);
            }
            onCreate(db);
        }
    }


	//////////////////////////////////////////////////////////////////////////////
	// Accessing Collections

	public MCollection[] getCollections(MendeleyConnector con) {
		
		MCollection [] result = null;
		
		try {
			Cursor cursor = m_database.query("COLLECTION", new String[] {"ID","NAME","TYPE","SIZE"}, null, null, null, null, "NAME");

			if (cursor.moveToFirst()) {
	
				result = new MCollection[cursor.getCount()];
				int i=0;
				do {
					result[i] = new MCollection(con);
					result[i].id   = cursor.getString(0);
					result[i].name = cursor.getString(1);
					result[i].type = cursor.getString(2);
					result[i].size = cursor.getInt(3);
					i++;
				} while(cursor.moveToNext());
			}
			cursor.close();
		} catch(Exception e) {
			Log.e("MendeleyConnection","Got exception "+e.getMessage());
		}
		
		return result;
	}    
	
	public long addCollection(MCollection mcol) {
		ContentValues vals = new ContentValues();
		
		vals.put("ID", mcol.id);
		vals.put("NAME", mcol.name);
		vals.put("TYPE", mcol.type);
		vals.put("SIZE", mcol.size);
		return m_database.insert("COLLECTION", null, vals);
	}

	public void updateCollections(MCollection[] collections) {
		for (int i=0; i<collections.length; i++) {
			addCollection(collections[i]);
		}
	}

	public long addDocToCollection(String docid, String colid) {
		ContentValues vals = new ContentValues();
		vals.put("DOCUMENTID", docid);
		vals.put("COLLECTIONID", colid);
		return m_database.insert("COLLECTIONDOCS", null, vals);
	}
	
	public String[] getCollection(MCollection col) {
		String [] result = null;
		
		Cursor cursor;
		if (col.m_coltype == MCollection.LIBRARY) {
			cursor = m_database.query("DOCUMENT", new String[] {"ID"}, null, null, null, null, "TITLE");
		} else if (col.m_coltype == MCollection.AUTHORED) {
			cursor = m_database.query("COLLECTIONDOCS", new String[] {"DOCUMENTID"}, "COLLECTIONID = 'AUTHORED'", null, null, null, null);
		} else {
			cursor = m_database.query("COLLECTIONDOCS", new String[] {"DOCUMENTID"}, "COLLECTIONID = '"+col.id+"'", null, null, null, null);
		}

		if (cursor.moveToFirst()) {
			result = new String[cursor.getCount()];
			int i=0;
			do {
				result[i] = cursor.getString(0);
				i++;
			} while(cursor.moveToNext());
		}
		cursor.close();
		
		return result;
	}
	
	public void updateCollectionDocs(MCollection col, String[] docids) {
		if (col.m_coltype == MCollection.LIBRARY) {
			// document will only be added if it is downloaded
			return;
		} 
		
		if (col.m_coltype == MCollection.AUTHORED) {
			col.id = "AUTHORED";
		} 
		
		for(int i=0; i<docids.length; i++) {
			addDocToCollection(docids[i], col.id);
		}
	}

	//////////////////////////////////////////////////////////////////////////////
	// Accessing Groups

	public MGroup[] getGroups(MendeleyConnector con) {
		
		MGroup [] result = null;
		
		try {
			Cursor cursor = m_database.query("GROUPS", new String[] {"ID","NAME","TYPE","SIZE"}, null, null, null, null, "NAME");

			if (cursor.moveToFirst()) {
	
				result = new MGroup[cursor.getCount()];
				int i=0;
				do {
					result[i] = new MGroup(con);
					result[i].id   = cursor.getString(0);
					result[i].name = cursor.getString(1);
					result[i].type = cursor.getString(2);
					result[i].size = cursor.getInt(3);
					i++;
				} while(cursor.moveToNext());
			}
			cursor.close();
		} catch(Exception e) {
			Log.e("MendeleyConnection","Got exception "+e.getMessage());
		}
		
		return result;
	}    
	
	public long addGroup(MGroup mgrp) {
		ContentValues vals = new ContentValues();
		
		vals.put("ID", mgrp.id);
		vals.put("NAME", mgrp.name);
		vals.put("TYPE", mgrp.type);
		vals.put("SIZE", mgrp.size);
		return m_database.insert("GROUPS", null, vals);
	}

	public void updateGroups(MGroup[] groups) {
		for (int i=0; i<groups.length; i++) {
			addGroup(groups[i]);
		}
	}

	public long addDocToGroup(String docid, String grid) {
		ContentValues vals = new ContentValues();
		vals.put("DOCUMENTID", docid);
		vals.put("GROUPID", grid);
		return m_database.insert("GROUPDOCS", null, vals);
	}
	
	public String[] getGroupDocIDs(MGroup col) {
		String [] result = null;
		
		Cursor cursor = m_database.query("GROUPDOCS", new String[] {"DOCUMENTID"}, "GROUPID = '"+col.id+"'", null, null, null, null);

		if (cursor.moveToFirst()) {
			result = new String[cursor.getCount()];
			int i=0;
			do {
				result[i] = cursor.getString(0);
				i++;
			} while(cursor.moveToNext());
		}
		cursor.close();
		
		return result;
	}
	
	public void updateGroupDocs(MGroup grp, String[] docids) {
		for(int i=0; i<docids.length; i++) {
			addDocToGroup(docids[i], grp.id);
		}
	}
	
	//////////////////////////////////////////////////////////////////////////////
	// Accessing Documents

	public MDocument getDocument(MDocument doc) {
		MDocument result = null;
		
		Log.i("DBConnector","Getting doc " + doc.m_docid);
		
		Cursor cursor = m_database.query("DOCUMENT", new String[] {"ID","TITLE","PUBLICATION_OUTLET","YEAR","ABSTRACT","URL","TYPE","NOTES"}, "ID = '" + doc.m_docid+"'", null, null, null, "TITLE","1");

		if (cursor.moveToFirst()) {
			result = doc;
			result.title = cursor.getString(1);
			result.publication_outlet = cursor.getString(2);
			result.year = cursor.getString(3);
			result.docabstract = cursor.getString(4);
			result.urls   = new String [] {cursor.getString(5)};
			result.type   = cursor.getString(6);
			result.notes  = cursor.getString(7);
		
			// the following code could be replaced by a JOIN
			Cursor authorCursor = m_database.query("DOCAUTHOR", new String[] {"DOCID","AUTID","POSITION"}, "DOCID="+doc.m_docid, null, null, null, "POSITION ASC");
			result.authors = new String [authorCursor.getCount()];
			if (authorCursor.moveToFirst()) {
				int i=0;
				do {
					Cursor autCur = m_database.query("AUTHOR", new String[] {"NAME"}, "ID = "+authorCursor.getInt(1), null, null, null, null,"1");
					if (autCur.moveToFirst()) {
						result.authors[i] = autCur.getString(0);
					}
					autCur.close();
					i++;
				} while(authorCursor.moveToNext());
			}
			authorCursor.close();
			
			// the following code could be replaced by a JOIN
			Cursor tagCursor = m_database.query("DOCTAG", new String[] {"DOCID","TAGID"}, "DOCID="+doc.m_docid, null, null, null, null);
			result.tags = new String [tagCursor.getCount()];
			if (tagCursor.moveToFirst()) {
				int i=0;
				do {
					long tagid = tagCursor.getInt(1);
					Cursor tagCur = m_database.query("TAG", new String[] {"NAME"}, "ID = "+tagid, null, null, null, null,"1");
					if (tagCur.moveToFirst()) {
						result.tags[i] = tagCur.getString(0);
					}
					tagCur.close();
					i++;
				} while(tagCursor.moveToNext());
			}
			tagCursor.close();
			
			Cursor idCursor = m_database.query("IDENTIFIER", new String[] {"TYPE","VALUE"}, "DOCID="+doc.m_docid, null, null, null, null);
			if (idCursor.moveToFirst()) {
				do {
					result.identifiers.put(idCursor.getString(0), idCursor.getString(1));
					
				} while(idCursor.moveToNext());
			}
			idCursor.close();
				
			Cursor discCursor = m_database.query("DISCIPLINE", new String[] {"TYPE","VALUE"}, "DOCID="+doc.m_docid, null, null, null, null);
			if (discCursor.moveToFirst()) {
				do {
					result.discipline.put(discCursor.getString(0), discCursor.getString(1));
					
				} while(discCursor.moveToNext());
			}
			discCursor.close();
				
		}
		cursor.close();
		
		return result;
	}
	
	public long addDocument(MDocument mdoc) {

		Log.i("DBConnector","Adding doc " + mdoc.m_docid);
		
		ContentValues vals = new ContentValues();
		vals.put("ID", mdoc.m_docid);
		vals.put("TITLE", mdoc.title);
		if(mdoc.publication_outlet != null) vals.put("PUBLICATION_OUTLET", mdoc.publication_outlet);
		vals.put("YEAR", mdoc.year);
		if(mdoc.identifiers != null) vals.put("DOI", mdoc.identifiers.get("DOI"));
		if(mdoc.docabstract != null) vals.put("ABSTRACT", mdoc.docabstract);
		if(mdoc.docabstract != null) vals.put("URL", mdoc.urls.toString());
		if(mdoc.docabstract != null) vals.put("TYPE", mdoc.type);
		if(mdoc.docabstract != null) vals.put("NOTES", mdoc.notes);
		
		//TODO: check whether author exists before adding
		if(mdoc.authors != null) {
			for (int i=0; i<mdoc.authors.length; i++) {
				ContentValues autvals = new ContentValues();
				autvals.put("NAME", mdoc.authors[i]);
				long autid = m_database.insert("AUTHOR", null, autvals);
				
				ContentValues autref = new ContentValues();
				autref.put("DOCID", mdoc.m_docid);
				autref.put("AUTID", autid);
				autref.put("POSITION", i);
				m_database.insert("DOCAUTHOR", null, autref);
			}
		}
		
		//TODO: check whether tag already exists
		if(mdoc.tags != null) {
			for (int i=0; i<mdoc.tags.length; i++) {
				ContentValues tagvals = new ContentValues();
				tagvals.put("NAME", mdoc.tags[i]);
				long tagid = m_database.insert("TAG", null, tagvals);
				
				ContentValues tagref = new ContentValues();
				tagref.put("DOCID", mdoc.m_docid);
				tagref.put("TAGID", tagid);
				m_database.insert("DOCTAG", null, tagref);
			}
		}
		
		for (String key : mdoc.identifiers.keySet()) {
			String value = mdoc.identifiers.get(key);

			ContentValues ref = new ContentValues();
			ref.put("DOCID", mdoc.m_docid);
			ref.put("TYPE", key);
			ref.put("VALUE", value);
			m_database.insert("IDENTIFIER", null, ref);
		}
		
		for (String key : mdoc.discipline.keySet()) {
			String value = mdoc.discipline.get(key);

			ContentValues ref = new ContentValues();
			ref.put("DOCID", mdoc.m_docid);
			ref.put("TYPE", key);
			ref.put("VALUE", value);
			m_database.insert("DISCIPLINE", null, ref);
		}
		
		return m_database.insert("DOCUMENT", null, vals);
	}

	public void updateDocument(MDocument result) {
		addDocument(result);
	}
}
